/*******************************************************************************
 * This file is part of blueprint4j www.blueprint4j.org.
 * 
 *     blueprint4j is free software: you can redistribute it and/or modify
 *     it under the terms of the GNU Lesser General Public License as published by
 *     the Free Software Foundation, either version 3 of the License, or
 *     (at your option) any later version.
 * 
 *     blueprint4j is distributed in the hope that it will be useful,
 *     but WITHOUT ANY WARRANTY; without even the implied warranty of
 *     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *     GNU Lesser General Public License for more details.
 * 
 *     You should have received a copy of the GNU Lesser General Public License
 *     along with blueprint4j. If not, see <http://www.gnu.org/licenses/>.
 ******************************************************************************/
package blueprint4j.db;

import java.io.File;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;

import org.hsqldb.server.Server;



import blueprint4j.utils.Log;
import blueprint4j.utils.Settings;
import blueprint4j.utils.ThreadScheduable;
import blueprint4j.utils.ThreadSchedule;
import blueprint4j.utils.Utils;

public class DataBaseUtilsHSQL extends DataBaseUtils implements ThreadScheduable {

	public static final String DEFAULT_DB_LOCATION_RELATIVE = "database/hsql/data";
	public static final File DEFAULT_DB_LOCATION;
	public static final int HSQL_DBPORT = Settings.getInt("database.hsql.port",9001);

	static {
		new File("database").mkdir();
		new File("database/hsql").mkdir();
		DEFAULT_DB_LOCATION = new File(DEFAULT_DB_LOCATION_RELATIVE);
		DEFAULT_DB_LOCATION.mkdir();
	}

	private static java.text.SimpleDateFormat simple_date_format = new java.text.SimpleDateFormat( "yyyy-MM-dd" );
	private static java.text.SimpleDateFormat simple_time_format = new java.text.SimpleDateFormat( "HH:mm:ss" );
	private static java.text.SimpleDateFormat simple_date_and_time_format = new java.text.SimpleDateFormat( "yyyy-MM-dd HH:mm:ss" );

	private  DBConnection dbconnect = null;
	private static Server hsql_server = null;

	public DataBaseUtilsHSQL() {}

	public DataBaseUtilsHSQL(DBConnection dbconnect) {
		this.dbconnect = dbconnect;
	}

	public SimpleDateFormat getDateTimeFormat() {
		return simple_date_and_time_format;
	}

	public SimpleDateFormat getTimeFormat() {
		return simple_time_format;
	}

	public SimpleDateFormat getDateFormat() {
		return simple_date_format;
	}

	/**
	 * This method reads the next index field from the index field table.
	 */
	public synchronized Long getNextIndexField(Entity entity)
	throws SQLException {
		Statement statement = DBTools.getLocalConnection(entity.getConnection()).createStatement();
		statement.execute("insert into " + IndexTable.TABLE_NAME + "(tablename) values ('" + entity.getTableName() + "')");
		ResultSet result_set = statement.executeQuery("CALL IDENTITY()");
		if (!result_set.next()) {
			throw new SQLException("Could not read the next inserted index field");
		}
		long value = result_set.getLong(1);
		result_set.close();
		statement.close();
		return new Long(value);
	}

	public void loadIdAfterInsert(DBConnection connection,FieldId id)
	throws SQLException {
		Statement smt = connection.createStatement();
		try {
			ResultSet rs = smt.executeQuery("CALL IDENTITY()");
			rs.next();
			id.setAsString(rs.getString(1));
		} finally {
			smt.close();
		}
	}

	public void createTable(DBConnection connection,Entity entity,VectorField table_fields)
	throws SQLException {
		createTable(connection,"create cached table",entity,table_fields);
	}

	public void createIndexTable(Statement st)
	throws SQLException {
		st.execute("create table d_index_table(index_field identity,tablename varchar(255) not null)");
	}

	/**
	* Convert a Date to SQL leave out the time
	*
	* @param date the Date to be converted
	* @return a string copy of the date ready to by used in the SQL including ' characters
	*/
	public String convertDateToSQLString(Date date) {
		if (date == null) {
			return "NULL";
		}
		return "'" + simple_date_format.format(date) + "'";
	}

	/**
	* Convert a Time to SQL leave out the date
	*
	* @param date the Date to be converted
	* @return a string copy of the date ready to by used in the SQL including ' characters
	*/
	public String convertTimeToSQLString(Date date) {
		if (date == null) {
			return "NULL";
		}
		return "'" + simple_time_format.format(date) + "'";
	}
	
	public String castTime(String date) {
		return "cast("+date+" AS TIME)";
	}

	/**
	* The SQL statement to retrieve the current date value
	*
	* @return the sql statement used to retrieve the current date value without time
	*/
	public String currentDateSQLMethod() {
		return "CURRENT_DATE";
	}

	/**
	* Convert a Date to SQL including time
	*
	* @param date the DateTime to be converted
	* @return a string copy of the date ready to by used in the SQL including ' characters
	*/
	public String convertDateTimeToSQLString(Date date) {
		if (date == null) {
			return "NULL";
		}
		return "'" + simple_date_and_time_format.format(date) + "'";
	}

	/**
	* The SQL statement to retrieve the current date value
	*
	* @return the sql statement used to retrieve the current date value with time
	*/
	public String currentDateTimeSQLMethod() {
		return "CURRENT_TIMESTAMP";
	}

	/**
	* Get the SQL command for top or first
	*
	* @param number The number of rows you would like back from the select.
	* @return Returns the SQL string command to limit the number of rows returned in a system
	* Note Mysql Can also retrieve the last amount,SELECT * FROM table LIMIT 95,-1; # Retrieve rows 96-last.
	*/
	public String sqlFirstString(String sql, int number) {
		if (sql.toUpperCase().indexOf("SELECT")!=-1) {
			return "select TOP " + number + " " + sql.substring(sql.toUpperCase().indexOf("SELECT")+"SELECT".length());
		} else {
			Log.debug.out("INCORRECT SQL LIMIT   = IN ["+sql+"] NO SELECT FOUND","");
			return null;
		}
	}

	/**
	* Converts a string value to a suitable value for an SQL statement
	*
	* @param source The value of the string
	* @return A converted string ready for use in SQL
	*/
	public String convertStringForSQL(String source) {
		return convertStringForSQLAsStatic(source);
	}

	private static String convertStringForSQLAsStatic(String source) {
		if (source == null) return null;
		return "'" + convertStringForSQLBeforeQuotes(source) + "'";
	}

	private static String convertStringForSQLBeforeQuotes(String source) {
		if (source == null) return null;
		return Utils.replaceAll(source, "'", "''");
	}

	/**
	* Converts a string after is has been returned from the database
	*
	* @param source The value of the string from the database
	* @return A converted string converted from the SQL syntax
	*/
	public String convertStringFromSQL(String source) {
		if (source == null) return null;
		return source;
	}

	public String dateSUB_HOUR(Date date,int hours) {
		return " date_sub("+convertDateTimeToSQLString(date)+", interval "+hours+" HOUR)";
	}

	public String dateSUB_MIN(Date date,int min) {
		return " date_sub("+convertDateTimeToSQLString(date)+", interval "+min+" MIN)";
	}

	public String dateSUB_SEC(Date date,int sec) {
		return " date_sub("+convertDateTimeToSQLString(date)+", interval "+sec+" SEC)";
	}

	public boolean mayUpdateFromResultSet(DBConnection dbcon) throws SQLException {
		return true;
	}

	public String convertTabnameToLocal(String tabnmame) {
		return tabnmame.toUpperCase();
	}

	public void executeKeepAliveSQL(DBConnection connection) throws SQLException {}

	public void executeOptimizeTable(DBConnection connection,String table_name)
	throws SQLException {}

	public void dropIndex(DBConnection connection,String table_name,String index_name)
	throws SQLException,DataException {
		if (!index_name.startsWith("SYS_PK")) {
			Statement smt = connection.createStatement();
			Log.trace.out("DATABASE UPDATE INDEX","DROP INDEX ["+index_name+"] ON TABLE ["+table_name+"]");
			connection.execute("drop index " + index_name + " if exists");
			smt.close();
		}
	}

	public String getSQLType(FieldAutoId field){return "bigint IDENTITY";}
	public String getSQLType(FieldBigDecimal field) {return "double";}
	public String getSQLType(FieldBigInteger field){return "bigint";}
	public String getSQLType(FieldBoolean field){return "char(1)";}
	public String getSQLType(FieldCharacter field){return "char(1)";}
	public String getSQLType(FieldColor field){return "char(6)";}
	public String getSQLType(FieldCurrency field){return "decimal(10,2)";}
	public String getSQLType(FieldDate field){return "date";}
	public String getSQLType(FieldDateTime field){return "datetime";}
	public String getSQLType(FieldDouble field){return "double";}
	public String getSQLType(FieldInteger field){return "int";}
	public String getSQLType(FieldLong field){return "bigint";}
	public String getSQLType(FieldString field){return "varchar("+field.getLength()+")";}
	public String getSQLType(FieldTime field){return "time";}
	public String getSQLType(FieldText field){return "varchar(2048)";}
	public String getSQLType(FieldImage field){return "LONGVARBINARY";}
	public String getSQLType(FieldProperties field){return "varchar(2048)";}
	public String getSQLType(FieldBlob field){return "LONGVARBINARY";}

	public void addField(DBConnection dbcon,Field field)
	throws SQLException {
		if (!field.mayFieldBeNull()) {
			dbcon.execute("alter table " + field.getEntity().getTableName() + " add column " + field.getName() + " " + getSQLType(field) + " not null");
		} else {
			dbcon.execute("alter table " + field.getEntity().getTableName() + " add column " + field.getName() + " " + getSQLType(field));
		}
	}

	/**
	 * HSQL has no build in modification tools
	 */
	public void modifyTableField(DBConnection connection,Field field)
	throws SQLException {
		// first drop all indexes on table
		boolean success = false;
		Log.trace.out("ALTER TABLE "+field.getEntity().getTableName(),"ALTER FIELD " + field.getName() + " " + getSQLType(field));
		Index.VectorIndex vi = connection.getIndexes().getIndexesFromTablename(field.getEntity().getTableName());
		for (int i = 0;i < vi.size();i++) {
			dropIndex(connection,field.getEntity().getTableName(),vi.get(i).index_name);
		}
		// now alter add column of correct type
		connection.execute("alter table " + field.getEntity().getTableName() + " add column tmp_"+field.getName() + " " + getSQLType(field));
		// now iupdate
		try {
			connection.execute("update "+field.getEntity().getTableName()+" set tmp_"+field.getName()+" = "+field.getName());
			connection.execute("alter table "+field.getEntity().getTableName()+" drop column " +field.getName());
			connection.execute("alter table "+field.getEntity().getTableName()+" alter column tmp_"+field.getName()+" rename to " +field.getName());
			success = true;
		} finally {
			if (!success) {
				connection.execute("alter table "+field.getEntity().getTableName()+" drop column tmp_" +field.getName());
			}
		}
		// now recreate all the indexes
		for (int i = 0;i < vi.size();i++) {
			if (!vi.get(i).index_name.startsWith("SYS_PK")) {
				connection.execute(getCreateIndexSQL(vi.get(i)));
			}
		}
	}

	public void modifyTableField(DBConnection connection,String old_name,Field field)
	throws SQLException {
		connection.execute("alter table " + field.getEntity().getTableName() + " alter column " + old_name + " RENAME TO " + field.getName());
	}


	public String getDropField(String name,String tablename) {
		return "alter table " + tablename + " drop column " + name;
	}

	protected Boolean isCorrectType(Field field,int type,int size)
	throws DataException {
		if (field instanceof FieldImage || field instanceof FieldBlob) {
			return new Boolean (type == java.sql.Types.LONGVARBINARY);
		} else {
			return super.isCorrectType(field,type,size);
		}
	}

	private static boolean isServerRunning() {
		try {
			new java.net.Socket("localhost",HSQL_DBPORT).close();
			return true;
		} catch (Throwable th) {
			return false;
		}
	}

	public static void startHsqlServer(String path,String dbname)
	throws SQLException {
		if (!isServerRunning()) {
			hsql_server = new Server();
			hsql_server.setAddress("localhost");
			hsql_server.setDatabaseName(0,dbname);
			hsql_server.setPort(HSQL_DBPORT);
			hsql_server.setTls(false);
			hsql_server.setSilent(true);
			hsql_server.setTrace(false);
			hsql_server.setDatabasePath(0,path+"/"+dbname);
			hsql_server.start();
			Log.trace.out("HSQL SERVER Started","HSQL DB + " + dbname + " " + hsql_server.getAddress() + " " + hsql_server.getDatabaseName(0,true) + " " + hsql_server.getDatabasePath(0,true));
		} else {
			Log.trace.out("HSQL SERVER Already Running","");
		}
	}
	
	/**
	 * Maintain the database
	 */
	public boolean keepAlive() {return true;}
	public int sleepTime() {return 1000*60*15;}
	public void process()
	throws Exception {
		// perform checkpoint
		Log.trace.out("UPDATE HSQL DB", "CHECKPOINT " + this);
		DBTools.getLocalConnection(dbconnect).execute("CHECKPOINT");
	}
	
	public void close()
	throws Exception {
		Log.trace.out("UPDATE HSQL DB", "SHUTDOWN");
		DBTools.getLocalConnection(dbconnect).execute("CHECKPOINT");
		//NOTE THIS NEEDS FIXING
		if (hsql_server != null) {
			DBTools.getLocalConnection(dbconnect).execute("SHUTDOWN COMPACT");
			hsql_server.stop();
		}
	}

	public void setupDatabaseServer(DBConnection connect) throws SQLException {
		ThreadSchedule.add(new DataBaseUtilsHSQL(connect),Thread.NORM_PRIORITY,ThreadSchedule.SHUTDOWN_LAST);
	}

	public boolean createDatabase(String path,String dbname,String username,String password)  {
		return true;
	}

	public void bulkLoadFile(DBConnection dbcon,String tablename,File file,String delimiter)
	throws SQLException {
		throw new SQLException("METHOD NOT IMPLEMENTED YET");
	}

	public void exportData(DBConnection dbcon,String select,File bulkfile)
	throws SQLException {
		throw new DataException("Not implemented yet");
	}

	public void backupDB(DBConnection dbcon,File file) throws Throwable {}
	public void restoreDB(DBConnection dbcon,File file) throws Throwable {}
}



